package generator

import generator.common.FreeMarker
import generator.entity.{Row, dataModel, dataModels}
import generator.util.{GenerateFromXSSFExcel, captureName, dataBaseConnect}
import org.apache.poi.xssf.usermodel.XSSFSheet

import java.io.File
import scala.collection.mutable.ListBuffer
import scala.util.matching.Regex

//添加cj1和cj2的来源用户，修改生成文件的文件名
object GeneratorSql {
  //  val FileName: String = "C:\\Users\\16624\\Documents\\WeChat Files\\wxid_u2jm0ieu550n22\\FileStorage\\File\\2023-09\\1.0与2.0表关系0813(1).xls"
  val FileName: String = "datas/1.0与2.0表关系.xls"
  val DevFileName: String = "datas/2.0到1.0档案梳理.xlsx"
  val writeFile = "datas/对应关系.xlsx"
  val sinkFuncFile = "datas/StreamSinkFunc.java"
  val mainFile = "datas/StreamMain.java"
  val packagePath = "E:\\program\\scala\\StreamTransform\\src\\main\\java\\com\\kafka\\"
  val maker:FreeMarker = new FreeMarker

  var user = "cj1"
  var pwd = "cj1"

  def getDataSourceDev(sheet:XSSFSheet):List[Row] = {
    val lastNum = sheet.getLastRowNum
    val list = ListBuffer[Row]()
//    val nameCell = sheet.getRow(0).getCell(8)
//    if (nameCell!=null) {
//      user=nameCell.toString
//      pwd=nameCell.toString
//    }

    for (a <- 1 to lastNum) {
      val row = sheet.getRow(a)
      var valid = row != null && row.getCell(3) != null && row.getCell(3).toString.trim.contains("是")
        if (valid) {

        val cj1_tableName = sheet.getSheetName.trim.toUpperCase
        val cj1_field = row.getCell(1).toString
        val cj1_dataType = dataTypeDev(cj1_tableName.toUpperCase, cj1_field.toUpperCase,user,pwd)

        val tableNamePattern = new Regex("([a-zA-Z_]+\\.)?([a-zA-Z_]+)")

        val cj2_tableName = tableNamePattern.findFirstMatchIn(row.getCell(4).toString).get.group(2)

        var cj2_exp = toSafeString(row.getCell(5).toString.trim.toUpperCase)
        var commentRow = ""
          if (row.getCell(6) != null) commentRow = row.getCell(6).toString
        val pattern = new Regex("(([A-Za-z_]+(,[A-Za-z_]+)*))$")

        if(!isValid(cj2_exp)) valid = false
        if (row.getCell(3).toString.trim.contains("?")) valid = false

        val matcher = pattern.findFirstMatchIn(cj2_exp.trim.replaceFirst("END$","#"))
        var cj2_field = ""

        var cj2_fieldList: List[String] = null
        val patternSub = new Regex("([A-Za-z_]+)")

        var isSelectExp = false


        if (!matcher.isEmpty) {
          cj2_field = matcher.get.group(2)
          cj2_exp = pattern.replaceFirstIn(cj2_exp, "") // 去掉cj2字段
          if (cj2_exp == "") cj2_exp = cj2_field

          cj2_fieldList = patternSub.findAllIn(matcher.get.group(1)).toList
          if (cj2_fieldList.size > 1) {
//            println(s"$cj1_tableName $cj1_field 需要2.0多个字段")
          }
        } else {
          val fieldFromExp = getField(cj2_exp)
          if (fieldFromExp==null) {
//            println(s"$cj1_tableName $cj1_field 需要指定采集2.0字段名")
            cj2_fieldList=List[String]()
            valid = false
          } else {
            cj2_fieldList = fieldFromExp
            cj2_field = fieldFromExp(0)
          }

        }
        var id = 0
        for (field<-cj2_fieldList) {
          var back = ""
          if (id != 0) back = id.toString
          id += 1
          cj2_exp = cj2_exp.replaceAll(field,s"@$back#")
        }
        val patternField = new Regex("@([A-Za-z_0-9]*)#")

        val setMergeList:ListBuffer[(String,String)] = ListBuffer[(String,String)]()
        cj2_exp = patternField.replaceAllIn(cj2_exp,matcher=>{
          //因为传入的是采集1.0实体类，需要修改，且只能包含一种采集2.0字段
  //        setMergeList.append((matcher.group(1),dataTypeDev(cj2_tableName,matcher.group(1),user,pwd)))
          var setDataType = cj1_dataType
          if (setDataType=="LongValue") setDataType = "Long"
          if (setDataType=="SqlDate") setDataType = "Date"
          setMergeList.append((captureName.captureName(cj1_field, true)+matcher.group(1),setDataType))
          "?"
        })


        list += Row(
          null,
          cj1_tableName,
          cj1_field.toUpperCase,
          cj1_dataType,
          null,
          toSafeString(commentRow),
          null,
          null,
          cj2_tableName.toUpperCase,
          cj2_field.toUpperCase,
          valid,
          captureName.captureName(cj1_field, true),
          captureName.captureName(cj2_field, true),
          cj2_exp.replaceAll(""+160.toChar," "),
          setMergeList.toList,
          dataBaseConnect.isNullable(cj1_tableName,cj1_field.toUpperCase)
        )
      }

    }
    list.toList
  }

  def getField(str:String):List[String] = {
    val funcList = List[String]("F_PRODUCE_MF_TRANSCODE","F_TRANSORG", "F_GET_CODE", "NVL", "DECODE", "F_TRANSCODE", "TO_CHAR", "TO_DATE")

    var field = ""
    for (func <- funcList) {
      val patternFunc = new Regex(s"$func\\((([A-Z_]+),?\\s*|'[^']*',?\\s*|[0-9]+,?\\s*)+\\)" + '$')
      val maybeMatch = patternFunc.findFirstMatchIn(str.trim)
      if (!maybeMatch.isEmpty) {
        return List[String](maybeMatch.get.group(2))
      }
    }
    val list = ListBuffer[String]()
    val empty = "\\s*\n*\\s*"
    val prefix = s"(CASE$empty)"
    val whenthen = s"(@([^#]*)#$empty([^\n@~]*)$empty)"
    val whenthens = s"($whenthen+)"
    val elseStr = s"(${empty}~${empty}([^\n@~]*))?"
    val End = '(' + s"${empty}END" + ')'
    val pattern = new Regex(s"\\(?${prefix + whenthens + elseStr + End}\\)?")

    val tarstr = str.replaceAll("WHEN\n?\\s?", "@").replaceAll("THEN\n?\\s?", "#").replaceAll("ELSE\n?\\s?", "~")

    val maybeMatch = pattern.findFirstMatchIn(tarstr)
    if (!maybeMatch.isEmpty) {
      val regex = new Regex(whenthen)
      pattern.findAllMatchIn(tarstr).foreach(matcher=>{
        regex.findAllMatchIn(matcher.group(2)).foreach(matcher2=>{
          val condition = matcher2.group(2)
          val patternCondition = new Regex(s"([a-zA-Z_]+)$empty=")
          val maybeMatch1 = patternCondition.findFirstMatchIn(condition)
          if (!maybeMatch1.isEmpty) {
            list.append(maybeMatch1.get.group(1))
          }
        })
      })
    }
    if (list.size==0) null
    else list.toSet.toList
  }

  def main(args: Array[String]): Unit = {
//    val generator = GenerateFromHSSFExcel[List[Row]](FileName, 1, getDataSource)
    //    generator.generate(generateTablesSql)
    //        generator.generate(generateTransformSql)
    //    generator.generate(generateMergeSql)
//        generator.generate(generateMult)
//    generator.generate(generateStreamMain)

    //"D_METER","D_LC_EQUIP","FC_GC","c_meter_read","c_meter_mp_rela","FC_CONS_GC_RELA","FC_GC_TYPE_CODE","g_TG","R_CP_CONS_RELA","R_CP_MP_RELA","C_MP","R_TMNL_RUN","R_CP","C_CONS","C_CONS_PRC","c_elec_addr","c_meter","G_LINE","G_LINE_RELA","G_LINE_TG_RELA","g_subs","g_subs_line_rela","c_mp_it_rela","C_CONS_CONTACT_RELA","C_CONTACT","C_CUST","c_it_run","c_ps","g_tran","C_ELEC_ADDR_DET"

    //"D_METER","D_LC_EQUIP","FC_GC","c_meter_read","c_meter_mp_rela","FC_CONS_GC_RELA","FC_GC_TYPE_CODE","g_TG","R_CP_CONS_RELA","R_CP_MP_RELA"
    //"C_MP","R_TMNL_RUN","R_CP","C_CONS","C_CONS_PRC","c_elec_addr","c_meter"

    //"G_LINE","G_LINE_RELA","G_LINE_TG_RELA","g_subs","g_subs_line_rela","c_mp_it_rela"
    // "C_CONS_CONTACT_RELA","C_CONTACT","C_CUST","c_it_run","c_ps","g_tran","C_ELEC_ADDR_DET"
    val tableList = List[String]("D_METER","D_LC_EQUIP","FC_GC","c_meter_read","c_meter_mp_rela","FC_CONS_GC_RELA","FC_GC_TYPE_CODE","g_TG","R_CP_CONS_RELA","R_CP_MP_RELA","C_MP","R_TMNL_RUN","R_CP","C_CONS","C_CONS_PRC","c_elec_addr","c_meter","G_LINE","G_LINE_RELA","G_LINE_TG_RELA","g_subs","g_subs_line_rela","c_mp_it_rela","C_CONS_CONTACT_RELA","C_CONTACT","C_CUST","c_it_run","c_ps","g_tran","C_ELEC_ADDR_DET")
    println(s"- ${tableList(0)}")
    val generatorDev = GenerateFromXSSFExcel[List[Row]](DevFileName, 2, getDataSourceDev,tableList(0))
    generatorDev.generate(generateMult)
    if (tableList.length>1) {
      for (a <- 1 until (tableList.length)) {
        println(s"- ${tableList(a)}")
        generatorDev.setSheetName(tableList(a))
        generatorDev.generate(generateMult)
      }
    }
  }

//  def getDataSource(sheet: HSSFSheet): List[Row] = {
//    val lastNum = sheet.getLastRowNum
//    val list = ListBuffer[Row]()
//    for (a <- 1 to lastNum) {
//      val row = sheet.getRow(a)
//      val field_type_detail_cell = row.getCell(14)
//      field_type_detail_cell.setCellType(CellType.STRING)
//      var field_type_detail = field_type_detail_cell.getStringCellValue
//      var field_type = row.getCell(15).getStringCellValue
//      val cj1_tableName = row.getCell(5).getStringCellValue
//      val cj1_field = row.getCell(6).getStringCellValue
//      val info = row.getCell(3).getStringCellValue
//      val comment = row.getCell(7).getStringCellValue
//      val commentRowCell = row.getCell(17)
//      val commentRowCellBack = row.getCell(19)
//      var commentRow = ""
//      if (commentRowCell != null) commentRow = commentRowCell.getStringCellValue
//      else if (commentRowCellBack != null) commentRow = commentRowCellBack.toString
//      val commentTable = row.getCell(2).getStringCellValue
//
//      val back_field_type_cell = row.getCell(13)
//      var back_field_type_detail_cell = row.getCell(12)
//
//      var back_field_type = ""
//      if (back_field_type_cell != null) back_field_type = back_field_type_cell.getStringCellValue
//
//      var back_field_type_detail = ""
//      if (back_field_type_detail_cell != null && back_field_type_detail_cell.getCellType.name == "ERROR") {
//        back_field_type_detail = back_field_type_detail_cell.toString
//        back_field_type_detail_cell = null
//      }
//      if (back_field_type_detail_cell != null) back_field_type_detail = back_field_type_detail_cell.toString
//
//      try {
//        back_field_type_detail = back_field_type_detail.toDouble.toInt.toString
//      } catch {
//        case _ => back_field_type_detail = ""
//      }
//
//      if (!isValid(field_type)) field_type = back_field_type
//      if (!isValid(field_type_detail) && (field_type != "DATE" || field_type != "date")) field_type_detail = back_field_type_detail
//      //      if (isValid(field_type))
//      var cj2_field = ""
//      if (row.getCell(11).toString != null) cj2_field = row.getCell(11).toString
//      val cj2_tableName = row.getCell(9).getStringCellValue
//      list += Row(
//        info,
//        cj1_tableName.toUpperCase,
//        cj1_field.toUpperCase,
//        dataTypeDev(cj1_tableName.toUpperCase,cj1_field.toUpperCase),
//        field_type_detail,
//        toSafeString(comment),
//        toSafeString(commentTable),
//        toSafeString(commentRow),
//        cj2_tableName.toUpperCase,
//        cj2_field.toUpperCase,
//        isValidDev(toSafeString(commentRow)),
//        captureName.captureName(cj1_field, true),
//        captureName.captureName(cj2_field, true)
//      )
//    }
//    list.toList
//  }


  def generateMult(list:List[Row]):Unit={
    list.groupBy(_.cj1_tableName).foreach {
      case (tableName, unsortlist) => {
        var flag = false //是否有效
        var MainPrimaryKey: Row = null
        var MainPrimaryField:(String,String) = dataBaseConnect.getPrimaryField(tableName)
        val modelList:ListBuffer[dataModel] = ListBuffer[dataModel]()
        var MainPrimaryflag = false //是否有主键

        var groupList = unsortlist.groupBy(_.cj2_tableName)
        var tarName = ""
        if (groupList.size==2 && groupList.contains("DEV")) {
          tarName=groupList.toList.apply(0)._1
          if (tarName=="DEV") {
            tarName=groupList.toList.apply(1)._1
          }
        }
        if (tarName!="") {
          groupList=unsortlist.groupBy(row=>{
            if (row.cj2_tableName=="DEV") {
              tarName
            } else row.cj2_tableName
          })
        }

        var singleFlag = groupList.size
        groupList.foreach{
          case(cj2_tableName,unsortlist2) => {

            var Sinkflag = false //是否有效
            var list2 = unsortlist2.sortBy(_.isValid)
            var primaryflag = false //是否有主键
            //var primaryField:(String,String) = dataBaseConnect.getPrimaryFieldCj2(cj2_tableName) //主键名和数据类型
            var primaryKey: Row = null
            for (i <- 0 until list2.length) {
              val row = list2(i)
              if (row.isValid) {
                flag = true
                Sinkflag = true
                if (row.comment == "主键" ||  MainPrimaryField._1 == row.cj1_field) {
                  primaryflag = true
                  primaryKey = row

                  MainPrimaryKey = row
                  MainPrimaryflag = true
                }
              }

            }
            if (primaryKey != null) list2 = list2.sortBy(_.cj1_field != primaryKey.cj1_field)
//            //如果数据库有主键而关系表中没有 则加入该主键
//            if (!primaryflag && isValid(MainPrimaryField._1) && isValid(primaryField._1)) {
//              primaryflag = true
//              MainPrimaryflag = true
//              primaryKey = Row(null,tableName,MainPrimaryField._1,dataType(MainPrimaryField._2),null,null,null,null,cj2_tableName,primaryField._1,true,captureName.captureName(MainPrimaryField._1,true),captureName.captureName(primaryField._1,true))
//              MainPrimaryKey = primaryKey
//              list2=list2:+primaryKey
//            }
            val Model: dataModel = dataModel(
              captureName.captureName(tableName, true),
              captureName.captureName(tableName, true).toLowerCase,
              captureName.captureName(tableName, true)+"SinkFunc",
              tableName.toUpperCase,
              list2,
              primaryKey,
              cj2_tableName.toUpperCase.replaceAll("_","")
            )

            //println(Model.className)
            if (Sinkflag && primaryflag) {
              val dir = new File(packagePath + Model.packageName)
              if (!dir.exists()) {
                dir.mkdir()
              }
//              if (singleFlag == 1) {
//                maker.makeTemplate("streamSinkFunc.ftl", Model, packagePath + Model.packageName + "\\" + Model.className+".java")
//                val ModelBack:dataModel = dataModel(
//                  Model.entity,Model.packageName,Model.className+"2",Model.tableName+"_FLINK",Model.Rows,Model.PrimaryRow,Model.cj2_tableName
//                )
//                maker.makeTemplate("streamSinkFunc.ftl", ModelBack, packagePath + ModelBack.packageName + "\\" + ModelBack.className + ".java")
//              }
//              else maker.makeTemplate("MultStreamSinkFunc.ftl", Model, packagePath + Model.packageName + "\\" + Model.className+".java")
              //println("----       生成成功               ----*")
            } else if(!primaryflag) {
              //println("----       主键未知                 ----*\n")
            } else {
              //println("----       没有有效字段               ----*\n")
            }


            modelList.append(Model)
          }
        }
        val Models = dataModels(
          dataModel(
            captureName.captureName(tableName, true),
            captureName.captureName(tableName, true).toLowerCase,
            captureName.captureName(tableName, true)+"_Main",
            tableName.toUpperCase,
            unsortlist.sortBy(_.isValid),
            MainPrimaryKey,
            unsortlist(0).cj2_tableName.replaceAll("_","")
          ),
          modelList
        )


        print("SELECT COUNT(*) ")
        var first = true
//        for (model <- Models.models) {
//          for (row <- model.Rows) {
//            if (row.isValid) {
//              if (first) {
//                print(' ')
//                first = false
//              }
//              else {
//                print(',')
//
//              }
//              print(row.cj1_field)
//            }
//          }
//        }
        println(s" FROM ${tableName.toUpperCase()};")
        if (flag && MainPrimaryflag) {
          val dir = new File(packagePath + Models.model.packageName)
          if (!dir.exists()) {
            dir.mkdir()
          }

//          if (singleFlag == 1) {
//            maker.makeTemplate("streamMain.ftl", Models.model, packagePath + Models.model.packageName + "\\" + Models.model.className+".java")
//          }
//          else {
//            maker.makeTemplate("MultStreamMain.ftl", Models, packagePath + Models.model.packageName + "\\" + Models.model.className+".java")
//          }
          //println("----       生成成功               ----*\n")
        } else if (!flag) {
          //println("----       没有有效字段               ----*\n")
        } else if (!MainPrimaryflag) {
          //println("----       主键未知                 ----*\n")
        }

      }
    }
  }

  def isValid(str: String): Boolean = {
    str != null && str != ""
  }

  def isValidField(str: String,details:String): Boolean = {
    ((str.contains("NUMBER") || str.contains("VARCHAR")) && details.matches("-?\\d+"))  ||
      (str.contains("DATE") || str.contains("date")) || str.matches("(?:VARCHAR2\\(\\d+\\)|NUMBER\\(\\d+\\)|NUMBER\\(\\d+,\\d+\\))")
  }
  def getValidField(str: String,details:String): String = {
    if ((str.contains("NUMBER") || str.contains("VARCHAR")) && details.matches("-?\\d+")) {
      s"$str($details)"
    } else if (str.contains("DATE") || str.contains("date")) {
      "DATE"
    } else str
  }
  def toSafeString(str:String) :String = {
    str.replace("\n", " ").replace("\r", " ")
  }
  def isValid(row: Row): Boolean = {
    row.field_type != null && row.field_type != "" &&
      (!isValid(row.commentRow) || !row.commentRow.contains("不处理")) &&
      isValidField(row.field_type,row.field_type_detail)//&&
    //(row.cj1_tableName != "C_IT_RUN" && row.cj1_tableName != "D_MEAS_BOX" && row.cj1_tableName != "S_IR_READ")
  }

  def isValidDev(row:Row):Boolean = {
    isValidDev(row.commentRow)
  }
  def isValidRule(rule:String): Boolean = {

    (isValid(rule) &&
      (
        !rule.contains("2.0无此表") &&
          !rule.contains("未迁移") &&
          !rule.contains("未知结构")
        )
      )
  }
  def isValidDev(commentRow: String): Boolean = {
    (!isValid(commentRow) ||
      (
        !commentRow.contains("不处理") &&
          !commentRow.contains("业务暂未涉及") &&
          !commentRow.contains("2.0无此业务") &&
          !commentRow.contains("无此字段") &&
          !commentRow.contains("用采数据库无此字段") &&
          !commentRow.contains("业务暂不使用") &&
          !commentRow.contains("没有") &&
          !commentRow.contains("暂不使用")

        )
      )
  }

  def toRef(str:String):String = {
    "+ \"" + str + "\"\n"
  }

  def dataType(str:String):String = {
    if (str.contains("NUMBER")) "LongValue"
    else if (str.contains("VARCHAR2")) "String"
    else if (str == "DataTypeNotFound") "DataTypeNotFound"
    else "SqlDate"
  }

//  def dataTypeDev(row:Row,user:String="cqcqdb",pwd:String="cqcqdb"):String = {
//    //    db.findDataType(row)
//    dataType(dataBaseConnect.findDataType(row,user,pwd))
//  }

  def dataTypeDev(tableName:String,field:String,user:String="cj1",pwd:String="cj1"): String = {
    //    db.findDataType(row)
    dataType(dataBaseConnect.findDataType(tableName,field,user,pwd))
  }
}
